{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9a65aae3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "()"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlLink,EnvSetting\n",
    "sql = f\"SELECT * FROM `{EnvSetting.user_db}`.`{EnvSetting.emailCheck_tab}` where `account`='test1';\"\n",
    "res = sqlLink.sqlGetData(EnvSetting.user_db,sql)\n",
    "res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "9674d4b8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1459178664.0\n",
      "time.struct_time(tm_year=2016, tm_mon=3, tm_mday=28, tm_hour=23, tm_min=24, tm_sec=24, tm_wday=5, tm_yday=88, tm_isdst=-1)\n",
      "time.struct_time(tm_year=2022, tm_mon=12, tm_mday=1, tm_hour=0, tm_min=17, tm_sec=49, tm_wday=3, tm_yday=335, tm_isdst=0)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import time\n",
    "# spawnTime = time.localtime()\n",
    "a = \"Sat Mar 28 23:24:24 2016\"\n",
    "print(time.mktime(time.strptime(a,\"%a %b %d %H:%M:%S %Y\")))\n",
    "print(time.strptime(a,\"%a %b %d %H:%M:%S %Y\"))\n",
    "# a1 = time.strptime(a,\"%a %b %d %H:%M:%S %Y\")\n",
    "# a2 = time.time() \n",
    "# print(time.localtime(a2))\n",
    "# time.strftime(\"%Y-%m-%d %H:%M:%S\", time.localtime(a2))\n",
    "end_time= \"2022-11-30 11:12:11\"\n",
    "end = time.strptime(end_time,\"%Y-%m-%d %H:%M:%S\")\n",
    "local_time = time.localtime()\n",
    "print(local_time)\n",
    "end > local_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3ef33c90",
   "metadata": {},
   "outputs": [
    {
     "ename": "RuntimeError",
     "evalue": "asyncio.run() cannot be called from a running event loop",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mRuntimeError\u001b[0m                              Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_23180\\2987948555.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m   \u001b[1;32mawait\u001b[0m \u001b[0masyncio\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msleep\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m2\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m   \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'2'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0masyncio\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[1;33m(\u001b[0m \u001b[0mtest\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'1'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python37\\lib\\asyncio\\runners.py\u001b[0m in \u001b[0;36mrun\u001b[1;34m(main, debug)\u001b[0m\n\u001b[0;32m     32\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mevents\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_get_running_loop\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     33\u001b[0m         raise RuntimeError(\n\u001b[1;32m---> 34\u001b[1;33m             \"asyncio.run() cannot be called from a running event loop\")\n\u001b[0m\u001b[0;32m     35\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     36\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mcoroutines\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0miscoroutine\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmain\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mRuntimeError\u001b[0m: asyncio.run() cannot be called from a running event loop"
     ]
    }
   ],
   "source": [
    "# 异步进程测试\n",
    "import time,asyncio\n",
    "async def test():\n",
    "  await asyncio.sleep(2)\n",
    "  print('2')\n",
    "asyncio.run(test())\n",
    "print('1')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5140242e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(221, b'Bye.')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from smtplib import SMTP_SSL\n",
    "from email.mime.text import MIMEText\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from email.header import Header\n",
    "\n",
    "msg = MIMEMultipart()\n",
    "msg['Subject'] = Header('test Header','utf-8')\n",
    "msg['From'] = 'itsite<it_introduce_test@qq.com>'\n",
    "msg[\"To\"] = '3463039720@qq.com'\n",
    "msg.attach(MIMEText('541A','html'))\n",
    "\n",
    "server = SMTP_SSL('smtp.qq.com')\n",
    "server.login('it_introduce_test@qq.com','vbwbhkdyavtfdaae')\n",
    "\n",
    "server.sendmail('it_introduce_test@qq.com','3463039720@qq.com',msg.as_string())\n",
    "server.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2e311cc0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'insert OK'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlLink\n",
    "account = \"test\\\\\" #\"\n",
    "sql = f\"UPDATE `itsite_user`.`user_info` SET `grade` = 1 WHERE `id` = {1};\"\n",
    "# sql2 = \"UPDATE `itsite_user`.`user_info` SET `grade` = 1 WHERE `id` = 2;\"\n",
    "sqlLink.InsertMultiData('itsite_user',[sql,sql2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "d2d2d981",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2022-11-19 18:53:25'"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlLink,time\n",
    "sql = f\"select account,token,cast(starttime as char),cast(endtime as char),clientid from `token` where account='test1';\"\n",
    "res = sqlLink.sqlGetData('itsite_user',sql)\n",
    "res[0][3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "6399eb1b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import time,sqlLink,EnvSetting\n",
    "local_time = time.localtime()\n",
    "user =  \"2022-11-16 17:13:59\"\n",
    "user_time = time.strptime(user,\"%Y-%m-%d %H:%M:%S\")\n",
    "# user_dot = time.mktime(user_time)\n",
    "local_time > user_time\n",
    "# local_time\n",
    "# sql = f\"select account,token,starttime,endtime,clientid from {EnvSetting.user_db}.{EnvSetting.token_tab} where account={account};\"\n",
    "# res = sqlLink.sqlGetData(EnvSetting.user_db,sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ec069c33",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['9aaa3c67bbd97c33b20054c06b95487512a900a34651b0c0849de52abaefa1b4',\n",
       " '2022-11-16 12:13:59',\n",
       " '2022-11-17 12:13:59']"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import userSign\n",
    "user = \"fsddfghfg\\h/;'!ghfgh\"\n",
    "userSign.tokenSpawn(user,3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "74cac0f3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2022-11-16 11:09:13\n",
      "2022-11-17 11:9:13\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'insert OK'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import time,sqlLink\n",
    "# isoTime = time.time()\n",
    "# print('a'+ str( isoTime).replace('.',''))\n",
    "spawnTime = time.localtime()\n",
    "endTime = f\"{spawnTime.tm_year}-{spawnTime.tm_mon}-{spawnTime.tm_mday+1} {spawnTime.tm_hour}:{spawnTime.tm_min}:{spawnTime.tm_sec}\"\n",
    "# isoTime.tm_hour\n",
    "print( time.strftime('%Y-%m-%d %H:%M:%S',spawnTime))\n",
    "print(endTime)\n",
    "\n",
    "sql = \"INSERT INTO `itsite_user`.`token` (`account`, `token`, `endtime`) VALUES ('test4', 'atasdf', '{}')\".format(endTime)\n",
    "\n",
    "sqlLink.InsertData('itsite_user',sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "206e8a5c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'9f5b3be4891c35310d31ca7ddfb9a6421a6bc3ba752e097587191101fbf0bafb'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import hmac\n",
    "salt = 'tee'\n",
    "submit_pwd = 'test'.encode('utf-8')\n",
    "hash = hmac.new(key=salt.encode('utf-8'),msg=submit_pwd,digestmod=\"sha256\")\n",
    "hash.hexdigest()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2fad12b5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(('test', 'c2efc2758c201d1c'),)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlLink,EnvSetting\n",
    "connect = sqlLink.sqlConncet(EnvSetting.user_db)\n",
    "cursour = connect.cursor()\n",
    "sql = \"SELECT user_account,pwd from info where user_account='test';\"\n",
    "sql2 = \"SELECT user_account,salt from key_tab where user_account='test';\"\n",
    "cursour.execute(sql)\n",
    "cursour.execute(sql2)\n",
    "cursour.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "251358fe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5\n",
      "11\n",
      "9\n",
      "12\n",
      "3\n",
      "8\n",
      "5\n",
      "6\n",
      "13\n",
      "6\n",
      "4\n",
      "15\n",
      "2\n",
      "4\n",
      "2\n",
      "10\n",
      "5b9c3856d64f242a\n"
     ]
    }
   ],
   "source": [
    "import random\n",
    "hex_str = ''\n",
    "for i in range(16):\n",
    "  num = random.randint(0,15)\n",
    "  print(num)\n",
    "  hex_str += hex(num)[2:]\n",
    "print(hex_str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "73fdbaf3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'20f65c28671b40937c5bf23acc7c6f37e5a5ec0622e347b57685725df5ba9e50'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import hashlib\n",
    "hash = hashlib.new(\"sha256\")\n",
    "hash.update(\"check\".encode('utf-8'))\n",
    "hash.hexdigest()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d485c7b2",
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "module 'userSign' has no attribute 'InsertMultiData'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_17092\\3289887173.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0msql2\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34m\"INSERT INTO `itsite_user`.`temporary_id` (`client_id`, `code`) VALUES ('test27', 'test');\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[0mli\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0msql\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0msql2\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m \u001b[0muserSign\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mInsertMultiData\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'itsite_user'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mli\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m: module 'userSign' has no attribute 'InsertMultiData'"
     ]
    }
   ],
   "source": [
    "import userSign\n",
    "sql = \"INSERT INTO `itsite_user`.`temporary_id` (`client_id`, `code`) VALUES ('test26', 'test');\"\n",
    "sql2 = \"INSERT INTO `itsite_user`.`temporary_id` (`client_id`, `code`) VALUES ('test27', 'test');\"\n",
    "\n",
    "li = [sql,sql2]\n",
    "print( userSign.InsertMultiData('itsite_user',li))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a61aa50e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "238ff0670b538bbac3456f6a689b49cdc97a37d21af3a5b416f45e1089a9cb64\n"
     ]
    }
   ],
   "source": [
    "import random,userSign,hmac\n",
    "hex_str = ''\n",
    "for i in range(16):\n",
    "\n",
    "  hex_str += hex(random.randint(0,16))[2:]\n",
    "salt = userSign.spawnSalt().encode('utf-8')\n",
    "hash = hmac.new(key=salt,msg=\"pwd\".encode('utf-8'),digestmod=\"sha256\")\n",
    "hash_str = hash.hexdigest()\n",
    "print(hash_str)\n",
    "# print(hex_str.encode('utf-8'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "de38e97f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1b082a6cecb21eec 密钥\n",
      "b'check'\n"
     ]
    }
   ],
   "source": [
    "import userSign\n",
    "passwd = \"57afcd6bc19505ced5540d416f995f79\"\n",
    "str_info = '{\"password\":\"57afcd6bc19505ced5540d416f995f79\",\"timestamp\":\"2022/11/6 09:28:49\",\"checkcode\":\"eec\"}'\n",
    "user_info = eval(str_info)\n",
    "pic_code = \"test\"\n",
    "timestamp = user_info['timestamp']\n",
    "rand_part = user_info['checkcode']\n",
    "word = user_info['password']\n",
    "# 复现密钥\n",
    "secret_key = userSign.spawnKey(pic_code,timestamp=timestamp,rand_part=rand_part)\n",
    "print(secret_key,'密钥')\n",
    "pwd = userSign.decryptWord(secret_key=secret_key,word=word)\n",
    "print(pwd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "27556c82",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2022-11-08 17:23:55\n",
      "INSERT INTO `itsite_user`.`temporary_id` (`client_id`, `code`) VALUES ('test26', 'test');\n",
      "INSERT INTO `itsite_user`.`temporary_id` (`client_id`, `code`) VALUES ('test28', 'test');\n",
      "INSERT INTO `itsite_user`.`temporary_id` (`client_id`, `code`) VALUES ('test29', 'test');\n"
     ]
    }
   ],
   "source": [
    "# 时间插入测试\n",
    "import time\n",
    "localtime = time.strftime(\"%Y-%m-%d %H:%M:%S\", time.localtime())\n",
    "print(localtime)\n",
    "import pymysql,time\n",
    "connect = pymysql.connect(\n",
    "        host='localhost',\n",
    "        port=3306,\n",
    "        user='user',\n",
    "        password='12345678',\n",
    "        db='itsite_user',\n",
    "        charset='utf8'\n",
    "    )\n",
    "\n",
    "\n",
    "print(sql)\n",
    "cursour = connect.cursor()\n",
    "# print \n",
    "# cursour.execute(sql)\n",
    "sql = \"INSERT INTO `itsite_user`.`temporary_id` (`client_id`, `code`) VALUES ('test28', 'test');\"\n",
    "sql2 = \"INSERT INTO `itsite_user`.`temporary_id` (`client_id`, `code`) VALUES ('test29', 'test');\"\n",
    "li = [sql,sql2]\n",
    "for sql in li:\n",
    "  print(sql)\n",
    "  cursour.execute(sql)\n",
    "# connect.commit()\n",
    "# cursour.execute(sql2)\n",
    "connect.commit()\n",
    "# result = cursour.fetchall()\n",
    "# print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5cea644e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2022-11-07 10:42:53\n",
      "error\n"
     ]
    }
   ],
   "source": [
    "# 测试时间戳传入mysql\n",
    "import pymysql,time,EnvSetting\n",
    "connect = pymysql.connect(\n",
    "        host='localhost',\n",
    "        port=3306,\n",
    "        user='user',\n",
    "        password='12345678',\n",
    "        db='itsite_user',\n",
    "        charset='utf8'\n",
    "    )\n",
    "nowtime = \"2022-11-07 10:42:53\"\n",
    "\"INSERT INTO `managerdb`.`adminlist`(`account`, `password`, `status`) VALUES ('{}', '{}', 1)\"\n",
    "cursour = connect.cursor()\n",
    "try:\n",
    "  cursour.execute(sql)\n",
    "  result = cursour.fetchall()\n",
    "except:\n",
    "  print('error')\n",
    "connect.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "43e9ff3a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2022-11-08 11:48:40\n",
      "((53, 'test', 'test', datetime.datetime(2022, 11, 8, 11, 48, 40)),)\n"
     ]
    }
   ],
   "source": [
    "import sqlLink\n",
    "sql = \"select * from temporary_id where id=53;\"\n",
    "res =  sqlLink.sqlGetData('itsite_user',sql)\n",
    "print(res[0][3])\n",
    "print(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "376cac35",
   "metadata": {},
   "outputs": [],
   "source": [
    "import string,random\n",
    "from captcha.image import ImageCaptcha\n",
    "asc_chars = string.ascii_letters\n",
    "numbers = string.digits\n",
    "rand_list = asc_chars + numbers\n",
    "code_list = random.sample(rand_list, 4)\n",
    "code_str = ''.join(code_list)\n",
    "image = ImageCaptcha().generate_image(code_str)\n",
    "image.save('static/{}.jpg'.format(code_str))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c597d794",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1b082a6cecb21eec b'check'\n"
     ]
    }
   ],
   "source": [
    "#二进制字符串转码测试\n",
    "string = b'{\"password\":\"57afcd6bc19505ced5540d416f995f79\",\"timestamp\":\"2022/11/6 09:28:49\",\"checkcode\":\"eec\"}'\n",
    "res = string.decode('utf-8')\n",
    "user_info = eval(res)\n",
    "pic_code = \"test\"\n",
    "timestamp = user_info['timestamp']\n",
    "rand_part = user_info['checkcode']\n",
    "word = user_info['password']\n",
    "import userSign\n",
    "secret_key = userSign.spawn_key(pic_code,timestamp=timestamp,rand_part=rand_part)\n",
    "pwd = userSign.decrypt_word(secret_key=secret_key,word=word)\n",
    "print(secret_key,pwd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c597d794",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "116101115116 <class 'str'> 10进制asc字符串\n",
      "2022/11/6 09:28:49\n",
      "b'1b082a6cecb21eec' 密钥字节码\n",
      "b'bade6838cbd716c3'\n",
      "b'W\\xaf\\xcdk\\xc1\\x95\\x05\\xce\\xd5T\\rAo\\x99_y'\n",
      "b'check\\x0b\\x0b\\x0b\\x0b\\x0b\\x0b\\x0b\\x0b\\x0b\\x0b\\x0b' 16 11\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "b'check'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#二进制字符串转码测试\n",
    "string = b'{\"password\":\"57afcd6bc19505ced5540d416f995f79\",\"timestamp\":\"2022/11/6 09:28:49\",\"checkcode\":\"eec\"}'\n",
    "res = string.decode('utf-8')\n",
    "user_info = eval(res)\n",
    "pic_code = \"test\"\n",
    "check_dec = \"\"\n",
    "for i in pic_code:\n",
    "  # print(i,ord(i),type(ord(i)))\n",
    "  asc_code = ord(i)\n",
    "  # print(asc_code,type(asc_code))\n",
    "  check_dec += str(asc_code)\n",
    "print(check_dec,type(check_dec),'10进制asc字符串')\n",
    "pic_part = hex(int(check_dec))\n",
    "# 提取时间\n",
    "timestamp = user_info['timestamp']\n",
    "print(timestamp)\n",
    "min = timestamp.split(' ')[1].split(':')[1]\n",
    "sec = timestamp.split(' ')[1].split(':')[2]\n",
    "time_num = min + sec\n",
    "time_part = hex(int(time_num))\n",
    "\n",
    "rand_part = user_info['checkcode']\n",
    "secret_key = pic_part[2:] + time_part[2:] + rand_part\n",
    "import hashlib\n",
    "md5_iv = hashlib.md5()\n",
    "byte_key = secret_key.encode('utf-8')\n",
    "print(byte_key,'密钥字节码')\n",
    "md5_iv.update(byte_key)\n",
    "iv_str = md5_iv.hexdigest()[0:16].encode('utf-8')\n",
    "print(iv_str)\n",
    "\n",
    "from Cryptodome.Cipher import AES\n",
    "import binascii\n",
    "# word = user_info['password'].encode('utf-8')\n",
    "word = binascii.unhexlify(user_info['password'])\n",
    "print(word)\n",
    "cipher2 = AES.new(byte_key, AES.MODE_CBC, iv=iv_str)\n",
    "user_pwd = cipher2.decrypt(word)\n",
    "print(user_pwd,len(user_pwd),user_pwd[-1])\n",
    "user_pwd[0:-user_pwd[-1]]\n",
    "# user_pwd.decode('utf-8')\n",
    "\n",
    "# secret_key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30d60563",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "check_dec = \"\"\n",
    "for i in pic_code:\n",
    "  # print(i,ord(i),type(ord(i)))\n",
    "  asc_code = ord(i)\n",
    "  # print(asc_code,type(asc_code))\n",
    "  check_dec += str(asc_code)\n",
    "print(check_dec,type(check_dec),'10进制asc字符串')\n",
    "pic_part = hex(int(check_dec))\n",
    "# 提取时间\n",
    "timestamp = user_info['timestamp']\n",
    "print(timestamp)\n",
    "min = timestamp.split(' ')[1].split(':')[1]\n",
    "sec = timestamp.split(' ')[1].split(':')[2]\n",
    "time_num = min + sec\n",
    "time_part = hex(int(time_num))\n",
    "\n",
    "rand_part = user_info['checkcode']\n",
    "secret_key = pic_part[2:] + time_part[2:] + rand_part\n",
    "import hashlib\n",
    "md5_iv = hashlib.md5()\n",
    "byte_key = secret_key.encode('utf-8')\n",
    "print(byte_key,'密钥字节码')\n",
    "md5_iv.update(byte_key)\n",
    "iv_str = md5_iv.hexdigest()[0:16].encode('utf-8')\n",
    "print(iv_str)\n",
    "\n",
    "from Cryptodome.Cipher import AES\n",
    "import binascii\n",
    "# word = user_info['password'].encode('utf-8')\n",
    "word = binascii.unhexlify(user_info['password'])\n",
    "print(word)\n",
    "cipher2 = AES.new(byte_key, AES.MODE_CBC, iv=iv_str)\n",
    "user_pwd = cipher2.decrypt(word)\n",
    "print(user_pwd,len(user_pwd),user_pwd[-1])\n",
    "user_pwd[0:-user_pwd[-1]]\n",
    "# user_pwd.decode('utf-8')\n",
    "\n",
    "# secret_key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "82e1a3a1",
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "(2003, \"Can't connect to MySQL server on 'localhost' ([WinError 10061] 由于目标计算机积极拒绝，无法连接。)\")",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mConnectionRefusedError\u001b[0m                    Traceback (most recent call last)",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36mconnect\u001b[1;34m(self, sock)\u001b[0m\n\u001b[0;32m    613\u001b[0m                             sock = socket.create_connection(\n\u001b[1;32m--> 614\u001b[1;33m                                 \u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhost\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mport\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnect_timeout\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    615\u001b[0m                             )\n",
      "\u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python37\\lib\\socket.py\u001b[0m in \u001b[0;36mcreate_connection\u001b[1;34m(address, timeout, source_address)\u001b[0m\n\u001b[0;32m    726\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0merr\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 727\u001b[1;33m         \u001b[1;32mraise\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    728\u001b[0m     \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python37\\lib\\socket.py\u001b[0m in \u001b[0;36mcreate_connection\u001b[1;34m(address, timeout, source_address)\u001b[0m\n\u001b[0;32m    715\u001b[0m                 \u001b[0msock\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mbind\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msource_address\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 716\u001b[1;33m             \u001b[0msock\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msa\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    717\u001b[0m             \u001b[1;31m# Break explicitly a reference cycle\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mConnectionRefusedError\u001b[0m: [WinError 10061] 由于目标计算机积极拒绝，无法连接。",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_16956\\143526377.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      8\u001b[0m               \u001b[0mpassword\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34mf'{user.passwd}'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      9\u001b[0m               \u001b[0mdb\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34mf'{sql.database}'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 10\u001b[1;33m               \u001b[0mcharset\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'utf8'\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     11\u001b[0m           )\n\u001b[0;32m     12\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mconnect\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, user, password, host, database, unix_socket, port, charset, sql_mode, read_default_file, conv, use_unicode, client_flag, cursorclass, init_command, connect_timeout, read_default_group, autocommit, local_infile, max_allowed_packet, defer_connect, auth_plugin_map, read_timeout, write_timeout, bind_address, binary_prefix, program_name, server_public_key, ssl, ssl_ca, ssl_cert, ssl_disabled, ssl_key, ssl_verify_cert, ssl_verify_identity, compress, named_pipe, passwd, db)\u001b[0m\n\u001b[0;32m    351\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_sock\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    352\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 353\u001b[1;33m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    354\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    355\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m__enter__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36mconnect\u001b[1;34m(self, sock)\u001b[0m\n\u001b[0;32m    662\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[0mDEBUG\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    663\u001b[0m                     \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mexc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtraceback\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 664\u001b[1;33m                 \u001b[1;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    665\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    666\u001b[0m             \u001b[1;31m# If e is neither DatabaseError or IOError, It's a bug.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mOperationalError\u001b[0m: (2003, \"Can't connect to MySQL server on 'localhost' ([WinError 10061] 由于目标计算机积极拒绝，无法连接。)\")"
     ]
    }
   ],
   "source": [
    "import pymysql,dataSolution,user,EnvSetting\n",
    "# source_db = initial_info.get(\"database\")\n",
    "# source_tab = initial_info.get(\"table\")\n",
    "source_db = EnvSetting.database\n",
    "# source_tab = \n",
    "data = dataSolution.loadInitialData('sql','category',source_db,source_tab)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac675ba6",
   "metadata": {},
   "outputs": [],
   "source": [
    "connect = pymysql.connect(\n",
    "              host='localhost',\n",
    "              port=3307,\n",
    "              user=f'{user.u_name}',\n",
    "              password=f'{user.passwd}',\n",
    "              db=f'{sql.database}',\n",
    "              charset='utf8'\n",
    "          )\n",
    "print(connect)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8f0bed94",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Missing optional dependency 'xlrd'. Install xlrd >= 1.0.0 for Excel support Use pip or conda to install xlrd.\n"
     ]
    }
   ],
   "source": [
    "import dataSolution,sqlLink\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "try:\n",
    "  pd.read_excel('data.xls',dtype={'node_id':'int','parent':'int'})\n",
    "except Exception as r:\n",
    "  print(str(r))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "20cbe36b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(3,    page_id page_name page_link  category_id\n",
      "5        6        编码     #test            3\n",
      "6        7        程序     #test            3\n",
      "7        8        代码     #test            3), (4,     page_id page_name page_link  category_id\n",
      "10       11     网络防火墙     #test            4), (5,    page_id page_name page_link  category_id\n",
      "0        1       服务器     #test            5\n",
      "1        2        PC     #test            5), (6,    page_id page_name page_link  category_id\n",
      "2        3       路由器     #test            6), (7,    page_id page_name page_link  category_id\n",
      "3        4     二层交换机     #test            7\n",
      "4        5     三层交换机     #test            7), (8,    page_id page_name page_link  category_id\n",
      "8        9   互联网(外网)     #test            8\n",
      "9       10   局域网(内网)     #test            8), (9,     page_id page_name page_link  category_id\n",
      "11       12     OSI七层     #test            9\n",
      "12       13    TCP/IP     #test            9)]\n",
      "[5, 6, 7, 3, 8, 4, 9]\n"
     ]
    }
   ],
   "source": [
    "import dataSolution,sqlLink\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "data = sqlLink.sqlGetData('itsite','SELECT * FROM page_info;')\n",
    "data = pd.DataFrame(data,columns=['page_id','page_name','page_link','category_id'])\n",
    "# print(data)\n",
    "print( list(data.groupby('category_id')) )\n",
    "l = data['category_id'].unique().tolist()\n",
    "print(l  )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2f07d798",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{3: [[6, '编码', '#test', 3], [7, '程序', '#test', 3], [8, '代码', '#test', 3]], 4: [[11, '网络防火墙', '#test', 4]], 5: [[1, '服务器', '#test', 5], [2, 'PC', '#test', 5]], 6: [[3, '路由器', '#test', 6]], 7: [[4, '二层交换机', '#test', 7], [5, '三层交换机', '#test', 7]], 8: [[9, '互联网(外网)', '#test', 8], [10, '局域网(内网)', '#test', 8]], 9: [[12, 'OSI七层', '#test', 9], [13, 'TCP/IP', '#test', 9]]}\n"
     ]
    }
   ],
   "source": [
    "page_dict = {}\n",
    "for cate in data.groupby('category_id'):\n",
    "  pages = []\n",
    "  for i in range(cate[1].shape[0]) :\n",
    "    \n",
    "    # print(i,cate[1].iloc[i],cate[1].iloc[i]['page_name'])\n",
    "    # page_list = [int(cate[1].iloc[i]['page_id']),str(page['page_name']),page['page_link'], page['category_id']]\n",
    "    page_list = [cate[1].iloc[i]['page_id'],cate[1].iloc[i]['page_name'],cate[1].iloc[i]['page_link'], cate[1].iloc[i]['category_id']]\n",
    "    pages.append(page_list)\n",
    "  page_dict[cate[0]] = pages\n",
    "print(page_dict)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "fdbc2d05",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   node_id       name  lft  rgt  parent\n",
      "0        1       信息技术    1   18       0\n",
      "1        2       电子设备    2    9       1\n",
      "2        5       资源设备    3    4       2\n",
      "3        6       通信设备    5    8       2\n",
      "4        7        交换机    6    7       6\n",
      "5        3         软件   10   11       1\n",
      "6        4       抽象概念   12   17       1\n",
      "7        8         网络   13   14       4\n",
      "8        9  计算机通信互联模型   15   16       4\n",
      "RES [{'name': '信息技术', 'parent': 0, 'children': [{'name': '电子设备', 'parent': 1, 'children': [{'name': '资源设备', 'parent': 2, 'children': []}]}]}]\n",
      "当前cur {'name': '电子设备', 'parent': 1, 'children': [{'name': '资源设备', 'parent': 2, 'children': []}]}\n",
      "RES [{'name': '信息技术', 'parent': 0, 'children': [{'name': '电子设备', 'parent': 1, 'children': [{'name': '资源设备', 'parent': 2, 'children': []}, {'name': '通信设备', 'parent': 2, 'children': [{'name': '交换机', 'parent': 6, 'children': []}]}]}]}]\n",
      "当前cur {'name': '通信设备', 'parent': 2, 'children': [{'name': '交换机', 'parent': 6, 'children': []}]}\n",
      "RES [{'name': '信息技术', 'parent': 0, 'children': [{'name': '电子设备', 'parent': 1, 'children': [{'name': '资源设备', 'parent': 2, 'children': []}, {'name': '通信设备', 'parent': 2, 'children': [{'name': '交换机', 'parent': 6, 'children': []}]}]}]}]\n",
      "当前cur {'name': '电子设备', 'parent': 1, 'children': [{'name': '资源设备', 'parent': 2, 'children': []}, {'name': '通信设备', 'parent': 2, 'children': [{'name': '交换机', 'parent': 6, 'children': []}]}]}\n",
      "RES [{'name': '信息技术', 'parent': 0, 'children': [{'name': '电子设备', 'parent': 1, 'children': [{'name': '资源设备', 'parent': 2, 'children': []}, {'name': '通信设备', 'parent': 2, 'children': [{'name': '交换机', 'parent': 6, 'children': []}]}]}]}]\n",
      "当前cur {'name': '信息技术', 'parent': 0, 'children': [{'name': '电子设备', 'parent': 1, 'children': [{'name': '资源设备', 'parent': 2, 'children': []}, {'name': '通信设备', 'parent': 2, 'children': [{'name': '交换机', 'parent': 6, 'children': []}]}]}]}\n",
      "RES [{'name': '信息技术', 'parent': 0, 'children': [{'name': '电子设备', 'parent': 1, 'children': [{'name': '资源设备', 'parent': 2, 'children': []}, {'name': '通信设备', 'parent': 2, 'children': [{'name': '交换机', 'parent': 6, 'children': []}]}]}, {'name': '软件', 'parent': 1, 'children': []}]}]\n",
      "当前cur {'name': '信息技术', 'parent': 0, 'children': [{'name': '电子设备', 'parent': 1, 'children': [{'name': '资源设备', 'parent': 2, 'children': []}, {'name': '通信设备', 'parent': 2, 'children': [{'name': '交换机', 'parent': 6, 'children': []}]}]}, {'name': '软件', 'parent': 1, 'children': []}]}\n",
      "RES [{'name': '信息技术', 'parent': 0, 'children': [{'name': '电子设备', 'parent': 1, 'children': [{'name': '资源设备', 'parent': 2, 'children': []}, {'name': '通信设备', 'parent': 2, 'children': [{'name': '交换机', 'parent': 6, 'children': []}]}]}, {'name': '软件', 'parent': 1, 'children': []}, {'name': '抽象概念', 'parent': 1, 'children': [{'name': '网络', 'parent': 4, 'children': []}]}]}]\n",
      "当前cur {'name': '抽象概念', 'parent': 1, 'children': [{'name': '网络', 'parent': 4, 'children': []}]}\n",
      "[{'name': '信息技术', 'parent': 0, 'children': [{'name': '电子设备', 'parent': 1, 'children': [{'name': '资源设备', 'parent': 2, 'children': []}, {'name': '通信设备', 'parent': 2, 'children': [{'name': '交换机', 'parent': 6, 'children': []}]}]}, {'name': '软件', 'parent': 1, 'children': []}, {'name': '抽象概念', 'parent': 1, 'children': [{'name': '网络', 'parent': 4, 'children': []}, {'name': '计算机通信互联模型', 'parent': 4, 'children': []}]}]}]\n"
     ]
    }
   ],
   "source": [
    "# data = dataSolution.loadData('excel','itsite','intest3')\n",
    "sql = 'SELECT * FROM catetest ORDER BY Lft;'\n",
    "data = sqlLink.sqlGetData('itsite',sql)\n",
    "# print(data)\n",
    "data = pd.DataFrame(data,columns=['node_id','name','lft','rgt','parent'])\n",
    "print(data)\n",
    "stack = []\n",
    "res = []\n",
    "def check_data(i):\n",
    "  if i > 3 and i < 7:\n",
    "    print('层级=',len(stack),data.loc[i]['name'] )\n",
    "    print(stack,res)\n",
    "    print('当前游标',cur)\n",
    "for i in data.index:\n",
    "  # check_data(i)\n",
    "  if len(stack) > 0:\n",
    "    while stack[-1] < data.loc[i]['rgt']:\n",
    "      # print('小于顶端',stack,data.loc[i][['name','rgt']])\n",
    "      stack.pop()\n",
    "      #重定向游标\n",
    "      cur = res[0]\n",
    "      for level in range(len(stack)-1):\n",
    "        cur = cur['children'][-1]\n",
    "      print('RES',res)\n",
    "      print('当前cur',cur)\n",
    "  if len(res) == 0:\n",
    "    # dict = {'node_id':data.loc[i]['node_id'],'name':data.loc[i]['name'],'lft':data.loc[i]['lft'],'rgt':data.loc[i]['rgt'],'parent':data.loc[i]['parent'],'children':[]}\n",
    "    dict = {'name':data.loc[i]['name'],'parent':data.loc[i]['parent'],'children':[]}\n",
    "    res.append(dict)\n",
    "    #让游标定为字典元素\n",
    "    cur = res[0]\n",
    "  else:\n",
    "    dict = {'name':data.loc[i]['name'],'parent':data.loc[i]['parent'],'children':[]}\n",
    "    #字典元素的子集列表添加一个字典\n",
    "    cur['children'].append(dict)\n",
    "    cur = cur['children'][-1]\n",
    "  stack.append(data.loc[i]['rgt'])\n",
    "  # if i > 3:\n",
    "  \n",
    "# for i in res[0]['children']:\n",
    "#   print('*******',i,'*******')\n",
    "print(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90252609",
   "metadata": {},
   "outputs": [],
   "source": [
    "{'name': '电子设备', 'parent': 1,\n",
    " 'children': [\n",
    "  {'name': '资源设备', 'parent': 2, 'children': []},\n",
    "  {'name': '通信设备', 'parent': 2,\n",
    "   'children': [{'name': '交换机', 'parent': 6, 'children': []}]\n",
    "   }\n",
    "   ]\n",
    "   }\n",
    "{'name': '电子设备', 'parent': 1, \n",
    "'children': [\n",
    "  {'name': '资源设备', 'parent': 2, 'children': []}, \n",
    "  {'name': '通信设备', 'parent': 2, \n",
    "  'children': [{'name': '交换机', 'parent': 6, 'children': []}]\n",
    "  }\n",
    "  ]\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "1d9bf2db",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{1: 'ia', 2: 'ib', 'child': [{1: 'inside'}]}\n",
      "{1: 'inside'}\n"
     ]
    }
   ],
   "source": [
    "test = [\n",
    "  {1:'a',2:'b','child':[\n",
    "    {1:'ia',2:'ib','child':[\n",
    "      {1:'inside'}\n",
    "    ]}\n",
    "    ]},\n",
    "  {1:'a1',2:'2b'}\n",
    "]\n",
    "cur = test[0]\n",
    "for i in range(2):\n",
    "  cur = cur['child'][0]\n",
    "  print(cur)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e534b387",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   node_id       name   lft   rgt  parent\n",
      "0      1.0       信息技术   1.0  18.0     0.0\n",
      "1      2.0       电子设备   1.0  18.0     1.0\n",
      "2      5.0       资源设备   2.0   9.0     2.0\n",
      "3      6.0       通信设备  10.0  11.0     2.0\n",
      "4      7.0        交换机  12.0  17.0     6.0\n",
      "5      3.0         软件   3.0   4.0     1.0\n",
      "6      4.0       抽象概念   5.0   8.0     1.0\n",
      "7      8.0         网络   6.0   7.0     4.0\n",
      "8      9.0  计算机通信互联模型  13.0  14.0     4.0\n",
      "9      NaN        NaN  15.0  16.0     NaN\n"
     ]
    },
    {
     "ename": "IntegrityError",
     "evalue": "(pymysql.err.IntegrityError) (1048, \"Column 'name' cannot be null\")\n[SQL: INSERT INTO categroy_tab (node_id, name, lft, rgt, parent) VALUES (%(node_id)s, %(name)s, %(lft)s, %(rgt)s, %(parent)s)]\n[parameters: ({'node_id': 1.0, 'name': '信息技术', 'lft': 1.0, 'rgt': 18.0, 'parent': 0.0}, {'node_id': 2.0, 'name': '电子设备', 'lft': 1.0, 'rgt': 18.0, 'parent': 1.0}, {'node_id': 5.0, 'name': '资源设备', 'lft': 2.0, 'rgt': 9.0, 'parent': 2.0}, {'node_id': 6.0, 'name': '通信设备', 'lft': 10.0, 'rgt': 11.0, 'parent': 2.0}, {'node_id': 7.0, 'name': '交换机', 'lft': 12.0, 'rgt': 17.0, 'parent': 6.0}, {'node_id': 3.0, 'name': '软件', 'lft': 3.0, 'rgt': 4.0, 'parent': 1.0}, {'node_id': 4.0, 'name': '抽象概念', 'lft': 5.0, 'rgt': 8.0, 'parent': 1.0}, {'node_id': 8.0, 'name': '网络', 'lft': 6.0, 'rgt': 7.0, 'parent': 4.0}, {'node_id': 9.0, 'name': '计算机通信互联模型', 'lft': 13.0, 'rgt': 14.0, 'parent': 4.0}, {'node_id': None, 'name': None, 'lft': 15.0, 'rgt': 16.0, 'parent': None})]\n(Background on this error at: https://sqlalche.me/e/14/gkpj)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mIntegrityError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[0;32m   1880\u001b[0m                     self.dialect.do_executemany(\n\u001b[1;32m-> 1881\u001b[1;33m                         \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1882\u001b[0m                     )\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\sqlalchemy\\dialects\\mysql\\mysqldb.py\u001b[0m in \u001b[0;36mdo_executemany\u001b[1;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[0;32m    192\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo_executemany\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 193\u001b[1;33m         \u001b[0mrowcount\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecutemany\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    194\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mcontext\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36mexecutemany\u001b[1;34m(self, query, args)\u001b[0m\n\u001b[0;32m    178\u001b[0m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmax_stmt_length\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 179\u001b[1;33m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_get_db\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mencoding\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    180\u001b[0m             )\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36m_do_execute_many\u001b[1;34m(self, prefix, values, postfix, args, max_stmt_length, encoding)\u001b[0m\n\u001b[0;32m    210\u001b[0m             \u001b[0msql\u001b[0m \u001b[1;33m+=\u001b[0m \u001b[0mv\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 211\u001b[1;33m         \u001b[0mrows\u001b[0m \u001b[1;33m+=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msql\u001b[0m \u001b[1;33m+\u001b[0m \u001b[0mpostfix\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    212\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrowcount\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mrows\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, query, args)\u001b[0m\n\u001b[0;32m    147\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 148\u001b[1;33m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_query\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    149\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_executed\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36m_query\u001b[1;34m(self, q)\u001b[0m\n\u001b[0;32m    309\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_clear_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 310\u001b[1;33m         \u001b[0mconn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mq\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    311\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_do_get_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36mquery\u001b[1;34m(self, sql, unbuffered)\u001b[0m\n\u001b[0;32m    547\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_execute_command\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mCOMMAND\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mCOM_QUERY\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msql\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 548\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_affected_rows\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_read_query_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    549\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_affected_rows\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36m_read_query_result\u001b[1;34m(self, unbuffered)\u001b[0m\n\u001b[0;32m    774\u001b[0m             \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mMySQLResult\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 775\u001b[1;33m             \u001b[0mresult\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    776\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_result\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36mread\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1155\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1156\u001b[1;33m             \u001b[0mfirst_packet\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_read_packet\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1157\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36m_read_packet\u001b[1;34m(self, packet_type)\u001b[0m\n\u001b[0;32m    724\u001b[0m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_result\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munbuffered_active\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 725\u001b[1;33m             \u001b[0mpacket\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_for_error\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    726\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mpacket\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\protocol.py\u001b[0m in \u001b[0;36mraise_for_error\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    220\u001b[0m             \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"errno =\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrno\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 221\u001b[1;33m         \u001b[0merr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_mysql_exception\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_data\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    222\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\err.py\u001b[0m in \u001b[0;36mraise_mysql_exception\u001b[1;34m(data)\u001b[0m\n\u001b[0;32m    142\u001b[0m         \u001b[0merrorclass\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mInternalError\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0merrno\u001b[0m \u001b[1;33m<\u001b[0m \u001b[1;36m1000\u001b[0m \u001b[1;32melse\u001b[0m \u001b[0mOperationalError\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 143\u001b[1;33m     \u001b[1;32mraise\u001b[0m \u001b[0merrorclass\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0merrno\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrval\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mIntegrityError\u001b[0m: (1048, \"Column 'name' cannot be null\")",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mIntegrityError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_13184\\2860928131.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[0mdataSolution\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrebuild_tree\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mres\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mres\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 6\u001b[1;33m \u001b[0msqlLink\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdataToSql\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mres\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'itsite'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'categroy_tab'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'append'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\sqlLink.py\u001b[0m in \u001b[0;36mdataToSql\u001b[1;34m(source, database, table, mode)\u001b[0m\n\u001b[0;32m     45\u001b[0m   \u001b[0mengine\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcreate_engine\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'mysql+pymysql://{}:{}@localhost:3306/{}?charset=utf8'\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0muser\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mu_name\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0muser\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpasswd\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mdatabase\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     46\u001b[0m   \u001b[1;31m# 数据导出到sql\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 47\u001b[1;33m   \u001b[0msource\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_sql\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'{}'\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtable\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcon\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mif_exists\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'{}'\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmode\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     48\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     49\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0m__name__\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m\"__main__\"\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36mto_sql\u001b[1;34m(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)\u001b[0m\n\u001b[0;32m   2880\u001b[0m             \u001b[0mchunksize\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2881\u001b[0m             \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2882\u001b[1;33m             \u001b[0mmethod\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2883\u001b[0m         )\n\u001b[0;32m   2884\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[1;34m(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method, engine, **engine_kwargs)\u001b[0m\n\u001b[0;32m    726\u001b[0m         \u001b[0mmethod\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    727\u001b[0m         \u001b[0mengine\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 728\u001b[1;33m         \u001b[1;33m**\u001b[0m\u001b[0mengine_kwargs\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    729\u001b[0m     )\n\u001b[0;32m    730\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[1;34m(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method, engine, **engine_kwargs)\u001b[0m\n\u001b[0;32m   1768\u001b[0m             \u001b[0mchunksize\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1769\u001b[0m             \u001b[0mmethod\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1770\u001b[1;33m             \u001b[1;33m**\u001b[0m\u001b[0mengine_kwargs\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1771\u001b[0m         )\n\u001b[0;32m   1772\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36minsert_records\u001b[1;34m(self, table, con, frame, name, index, schema, chunksize, method, **engine_kwargs)\u001b[0m\n\u001b[0;32m   1348\u001b[0m                 \u001b[1;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"inf cannot be used with MySQL\"\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1349\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1350\u001b[1;33m                 \u001b[1;32mraise\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1351\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1352\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36minsert_records\u001b[1;34m(self, table, con, frame, name, index, schema, chunksize, method, **engine_kwargs)\u001b[0m\n\u001b[0;32m   1338\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1339\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1340\u001b[1;33m             \u001b[0mtable\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1341\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mSQLAlchemyError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1342\u001b[0m             \u001b[1;31m# GH34431\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36minsert\u001b[1;34m(self, chunksize, method)\u001b[0m\n\u001b[0;32m    965\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    966\u001b[0m                 \u001b[0mchunk_iter\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mzip\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0marr\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mstart_i\u001b[0m\u001b[1;33m:\u001b[0m\u001b[0mend_i\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0marr\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mdata_list\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 967\u001b[1;33m                 \u001b[0mexec_insert\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkeys\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mchunk_iter\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    968\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    969\u001b[0m     def _query_iterator(\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36m_execute_insert\u001b[1;34m(self, conn, keys, data_iter)\u001b[0m\n\u001b[0;32m    880\u001b[0m         \"\"\"\n\u001b[0;32m    881\u001b[0m         \u001b[0mdata\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mdict\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mzip\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkeys\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mrow\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mrow\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mdata_iter\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 882\u001b[1;33m         \u001b[0mconn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtable\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdata\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    883\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    884\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_execute_insert_multi\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconn\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkeys\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mlist\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mstr\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdata_iter\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[0;32m   1378\u001b[0m             )\n\u001b[0;32m   1379\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1380\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0m_EMPTY_EXECUTION_OPTS\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1381\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1382\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_execute_function\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\sqlalchemy\\sql\\elements.py\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[1;34m(self, connection, multiparams, params, execution_options, _force)\u001b[0m\n\u001b[0;32m    332\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0m_force\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msupports_execution\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    333\u001b[0m             return connection._execute_clauseelement(\n\u001b[1;32m--> 334\u001b[1;33m                 \u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    335\u001b[0m             )\n\u001b[0;32m    336\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[1;34m(self, elem, multiparams, params, execution_options)\u001b[0m\n\u001b[0;32m   1580\u001b[0m             \u001b[0melem\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1581\u001b[0m             \u001b[0mextracted_params\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1582\u001b[1;33m             \u001b[0mcache_hit\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mcache_hit\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1583\u001b[0m         )\n\u001b[0;32m   1584\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mhas_events\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[0;32m   1942\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1943\u001b[0m             self._handle_dbapi_exception(\n\u001b[1;32m-> 1944\u001b[1;33m                 \u001b[0me\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1945\u001b[0m             )\n\u001b[0;32m   1946\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[1;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[0;32m   2123\u001b[0m             \u001b[1;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2124\u001b[0m                 util.raise_(\n\u001b[1;32m-> 2125\u001b[1;33m                     \u001b[0msqlalchemy_exception\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m2\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0me\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2126\u001b[0m                 )\n\u001b[0;32m   2127\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\sqlalchemy\\util\\compat.py\u001b[0m in \u001b[0;36mraise_\u001b[1;34m(***failed resolving arguments***)\u001b[0m\n\u001b[0;32m    206\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    207\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 208\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    209\u001b[0m         \u001b[1;32mfinally\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    210\u001b[0m             \u001b[1;31m# credit to\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[0;32m   1879\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1880\u001b[0m                     self.dialect.do_executemany(\n\u001b[1;32m-> 1881\u001b[1;33m                         \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1882\u001b[0m                     )\n\u001b[0;32m   1883\u001b[0m             \u001b[1;32melif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mparameters\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mno_parameters\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\sqlalchemy\\dialects\\mysql\\mysqldb.py\u001b[0m in \u001b[0;36mdo_executemany\u001b[1;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[0;32m    191\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    192\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo_executemany\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 193\u001b[1;33m         \u001b[0mrowcount\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecutemany\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    194\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mcontext\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    195\u001b[0m             \u001b[0mcontext\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_rowcount\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mrowcount\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36mexecutemany\u001b[1;34m(self, query, args)\u001b[0m\n\u001b[0;32m    177\u001b[0m                 \u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    178\u001b[0m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmax_stmt_length\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 179\u001b[1;33m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_get_db\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mencoding\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    180\u001b[0m             )\n\u001b[0;32m    181\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36m_do_execute_many\u001b[1;34m(self, prefix, values, postfix, args, max_stmt_length, encoding)\u001b[0m\n\u001b[0;32m    209\u001b[0m                 \u001b[0msql\u001b[0m \u001b[1;33m+=\u001b[0m \u001b[1;34mb\",\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    210\u001b[0m             \u001b[0msql\u001b[0m \u001b[1;33m+=\u001b[0m \u001b[0mv\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 211\u001b[1;33m         \u001b[0mrows\u001b[0m \u001b[1;33m+=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msql\u001b[0m \u001b[1;33m+\u001b[0m \u001b[0mpostfix\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    212\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrowcount\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mrows\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    213\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mrows\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, query, args)\u001b[0m\n\u001b[0;32m    146\u001b[0m         \u001b[0mquery\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmogrify\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0margs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    147\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 148\u001b[1;33m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_query\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    149\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_executed\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    150\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36m_query\u001b[1;34m(self, q)\u001b[0m\n\u001b[0;32m    308\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_last_executed\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mq\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    309\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_clear_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 310\u001b[1;33m         \u001b[0mconn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mq\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    311\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_do_get_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    312\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrowcount\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36mquery\u001b[1;34m(self, sql, unbuffered)\u001b[0m\n\u001b[0;32m    546\u001b[0m             \u001b[0msql\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msql\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mencode\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mencoding\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"surrogateescape\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    547\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_execute_command\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mCOMMAND\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mCOM_QUERY\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msql\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 548\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_affected_rows\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_read_query_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    549\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_affected_rows\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    550\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36m_read_query_result\u001b[1;34m(self, unbuffered)\u001b[0m\n\u001b[0;32m    773\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    774\u001b[0m             \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mMySQLResult\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 775\u001b[1;33m             \u001b[0mresult\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    776\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_result\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    777\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mserver_status\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36mread\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1154\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1155\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1156\u001b[1;33m             \u001b[0mfirst_packet\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_read_packet\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1157\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1158\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mfirst_packet\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mis_ok_packet\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36m_read_packet\u001b[1;34m(self, packet_type)\u001b[0m\n\u001b[0;32m    723\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_result\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_result\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munbuffered_active\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    724\u001b[0m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_result\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munbuffered_active\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 725\u001b[1;33m             \u001b[0mpacket\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_for_error\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    726\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mpacket\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    727\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\protocol.py\u001b[0m in \u001b[0;36mraise_for_error\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    219\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mDEBUG\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    220\u001b[0m             \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"errno =\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrno\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 221\u001b[1;33m         \u001b[0merr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_mysql_exception\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_data\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    222\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    223\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdump\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32md:\\1LinShiWenJianJia\\Itpypro\\venv\\lib\\site-packages\\pymysql\\err.py\u001b[0m in \u001b[0;36mraise_mysql_exception\u001b[1;34m(data)\u001b[0m\n\u001b[0;32m    141\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0merrorclass\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    142\u001b[0m         \u001b[0merrorclass\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mInternalError\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0merrno\u001b[0m \u001b[1;33m<\u001b[0m \u001b[1;36m1000\u001b[0m \u001b[1;32melse\u001b[0m \u001b[0mOperationalError\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 143\u001b[1;33m     \u001b[1;32mraise\u001b[0m \u001b[0merrorclass\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0merrno\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrval\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mIntegrityError\u001b[0m: (pymysql.err.IntegrityError) (1048, \"Column 'name' cannot be null\")\n[SQL: INSERT INTO categroy_tab (node_id, name, lft, rgt, parent) VALUES (%(node_id)s, %(name)s, %(lft)s, %(rgt)s, %(parent)s)]\n[parameters: ({'node_id': 1.0, 'name': '信息技术', 'lft': 1.0, 'rgt': 18.0, 'parent': 0.0}, {'node_id': 2.0, 'name': '电子设备', 'lft': 1.0, 'rgt': 18.0, 'parent': 1.0}, {'node_id': 5.0, 'name': '资源设备', 'lft': 2.0, 'rgt': 9.0, 'parent': 2.0}, {'node_id': 6.0, 'name': '通信设备', 'lft': 10.0, 'rgt': 11.0, 'parent': 2.0}, {'node_id': 7.0, 'name': '交换机', 'lft': 12.0, 'rgt': 17.0, 'parent': 6.0}, {'node_id': 3.0, 'name': '软件', 'lft': 3.0, 'rgt': 4.0, 'parent': 1.0}, {'node_id': 4.0, 'name': '抽象概念', 'lft': 5.0, 'rgt': 8.0, 'parent': 1.0}, {'node_id': 8.0, 'name': '网络', 'lft': 6.0, 'rgt': 7.0, 'parent': 4.0}, {'node_id': 9.0, 'name': '计算机通信互联模型', 'lft': 13.0, 'rgt': 14.0, 'parent': 4.0}, {'node_id': None, 'name': None, 'lft': 15.0, 'rgt': 16.0, 'parent': None})]\n(Background on this error at: https://sqlalche.me/e/14/gkpj)"
     ]
    }
   ],
   "source": [
    "\n",
    "sql = \"SELECT * FROM `{}`.`{}`\".format('itsite','test_copy1')\n",
    "data = pd.DataFrame( sqlLink.sqlGetData('itsite',sql),columns=['node_id','name','lft','rgt','parent'])\n",
    "res = data.copy()\n",
    "dataSolution.rebuild_tree(1,1,res)\n",
    "print(res)\n",
    "sqlLink.dataToSql(res,'itsite','categroy_tab','append')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "08bf007c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   node_id       name parent\n",
      "0        1       电子设备    NaN\n",
      "1        2       资源设备   电子设备\n",
      "2        3       通信设备   电子设备\n",
      "3        4        交换机   通信设备\n",
      "4        5         软件    NaN\n",
      "5        6       抽象概念    NaN\n",
      "6        7         网络     软件\n",
      "7        8  计算机通信互联模型     软件\n",
      "{'电子设备': 1, '资源设备': 2, '通信设备': 3, '交换机': 4, '软件': 5, '抽象概念': 6, '网络': 7, '计算机通信互联模型': 8}\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0    1\n",
       "1    2\n",
       "2    3\n",
       "3    4\n",
       "4    5\n",
       "5    6\n",
       "6    7\n",
       "7    8\n",
       "Name: name, dtype: int32"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pymysql\n",
    "import pandas as pd\n",
    "connect = pymysql.connect(\n",
    "            host='localhost',\n",
    "            port=3306,\n",
    "            user='user',\n",
    "            password='12345678',\n",
    "            db='itsite',\n",
    "            charset='utf8'\n",
    "        )\n",
    "cursour = connect.cursor()\n",
    "# sql = \"SELECT * FROM `itsite`.`test_copy1`\"\n",
    "# cursour.execute(sql)\n",
    "# result = cursour.fetchall()\n",
    "# print(result)\n",
    "data = pd.read_excel('初始数据.xlsx',dtype={'node_id':'int'})\n",
    "name_dict = {}\n",
    "print(data)\n",
    "for i in data.index:\n",
    "  name_dict[data.loc[i,'name']]=data.loc[i,'node_id']\n",
    "  # print(i)\n",
    "print(name_dict)\n",
    "data['name'].map(name_dict)\n",
    "# from sqlalchemy import create_engine\n",
    "\n",
    "# engine = create_engine('mysql+pymysql://user:12345678@localhost:3306/itsite?charset=utf8')\n",
    "# # out_res.to_sql(name='intest', con=engine,if_exists='replace',index=False)\n",
    "# data.to_sql(name='intest2', con=engine,if_exists='append',index=False)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "bada0143",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'result' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_7680\\2495589156.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'name'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'lft'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'rgt'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'father'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mindex\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mindex\u001b[0m \u001b[1;33m+\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m#--------插入前更新左右值------------\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m data = [{'name':'cherry2','lft':None,'father':5},\n",
      "\u001b[1;31mNameError\u001b[0m: name 'result' is not defined"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame(result,columns=['id','name','lft','rgt','father'])\n",
    "df.index = df.index +1\n",
    "print(df)\n",
    "#--------插入前更新左右值------------\n",
    "data = [{'name':'cherry2','lft':None,'father':5},\n",
    "{'name':'cherry3','lft':None,'father':3},\n",
    "{'name':'bana2','lft':None,'father':3}]\n",
    "near_nodes_id = []\n",
    "for i in data:\n",
    "  near_node = df.query('father=={}'.format(i['father'])).iloc[-1]['rgt']\n",
    "  near_nodes_id.append(near_node)\n",
    "else:\n",
    "  father_nodes = pd.Series(near_nodes_id)\n",
    "print(father_nodes)\n",
    "# print(near_nodes_id)\n",
    "# father_nodes = father_nodes.sort_values(ascending=False)\n",
    "end_nodes = father_nodes.value_counts().sort_index(ascending=False).index\n",
    "nodes_count = father_nodes.value_counts().sort_index(ascending=False).values\n",
    "\n",
    "# father_nodes.value_counts()\n",
    "# father_nodes\n",
    "new = df.copy()\n",
    "tab_name = 'intest'\n",
    "import sqlLink\n",
    "db = sqlLink.sqlConncet('itsite')\n",
    "\n",
    "for end in range(0,len(end_nodes)) :\n",
    "  new.loc[new['lft']>end_nodes[end],'lft']+=2 * nodes_count[end]\n",
    "\n",
    "  new.loc[new['rgt']>end_nodes[end],'rgt']+=2 * nodes_count[end]\n",
    "  sql = f\"UPDATE {tab_name} SET lft = lft + 2*{nodes_count[end]} WHERE lft > {end_nodes[end]};\"\n",
    "  sql2 = f\"UPDATE {tab_name} SET rgt = rgt + 2*{nodes_count[end]} WHERE rgt > {end_nodes[end]};\"\n",
    "  cursor = db.cursor()\n",
    "  try:\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(sql)\n",
    "    cursor.execute(sql2)\n",
    "    # 提交到数据库执行\n",
    "    db.commit()\n",
    "  except:\n",
    "    # 发生错误时回滚\n",
    "    db.rollback()\n",
    "    print('执行失败')\n",
    "    break\n",
    "  \n",
    "  # sql = f\"UPDATE {tab_name} SET rgt = rgt + 2*{nodes_count[end]} WHERE rgt > {end_nodes[end]};\"\n",
    "\n",
    "db.close()\n",
    "print(new)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "682a8f40",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   0       1   2   3  4\n",
      "0  6  banana  12  13  5 同级节点\n",
      "   0       1  2  3  4\n",
      "0  4  Cherry  4  5  3 同级节点\n",
      "    0        1  2  3  4\n",
      "0   4   Cherry  4  5  3\n",
      "1  11  cherry3  6  7  3 同级节点\n"
     ]
    }
   ],
   "source": [
    "# -----sql插入新数据---------\n",
    "import sqlLink\n",
    "db = sqlLink.sqlConncet('itsite')\n",
    "cursor = db.cursor()\n",
    "for node in data:\n",
    "  # new.query('father=={}'.format(node['father']))\n",
    "  sql = \"SELECT * FROM intest WHERE `level`={};\".format(node['father'])\n",
    "  level_nodes = pd.DataFrame(sqlLink.sqlGetData('itsite',sql))\n",
    "  print(level_nodes,'同级节点')\n",
    "  end_rgt = level_nodes.iloc[-1,3]\n",
    "  sql = \"INSERT INTO itsite.intest (`name`,`Lft`,`Rgt`,`level`) VALUES ('{}',{},{},{});\".format(node['name'],end_rgt+1,end_rgt+2,node['father'])\n",
    "  # print(sql)\n",
    "  try:\n",
    "   # 执行sql语句\n",
    "    cursor.execute(sql)\n",
    "    # 提交到数据库执行\n",
    "    db.commit()\n",
    "  except:\n",
    "    # 如果发生错误则回滚\n",
    "    db.rollback()\n",
    "    print('执行错误')\n",
    " \n",
    "# 关闭数据库连接\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "1cfeb0bc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    id     name   lft  rgt  father\n",
      "5  6.0   banana     8  9.0       5\n",
      "9  NaN  cherry2  None  NaN       5 同级节点\n",
      "9 编辑ID id            NaN\n",
      "name      cherry2\n",
      "lft          None\n",
      "rgt           NaN\n",
      "father          5\n",
      "Name: 9, dtype: object 对应原表节点 9.0 end值\n",
      "     id     name   lft  rgt  father\n",
      "3   4.0   Cherry     4  5.0       3\n",
      "10  NaN  cherry3  None  NaN       3\n",
      "11  NaN    bana2  None  NaN       3 同级节点\n",
      "10 编辑ID id            NaN\n",
      "name      cherry3\n",
      "lft          None\n",
      "rgt           NaN\n",
      "father          3\n",
      "Name: 10, dtype: object 对应原表节点 5.0 end值\n",
      "     id     name   lft  rgt  father\n",
      "3   4.0   Cherry     4  5.0       3\n",
      "10  NaN  cherry3   6.0  7.0       3\n",
      "11  NaN    bana2  None  NaN       3 同级节点\n",
      "11 编辑ID id          NaN\n",
      "name      bana2\n",
      "lft        None\n",
      "rgt         NaN\n",
      "father        3\n",
      "Name: 11, dtype: object 对应原表节点 5.0 end值\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>lft</th>\n",
       "      <th>rgt</th>\n",
       "      <th>father</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Food</td>\n",
       "      <td>1</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>Fruit</td>\n",
       "      <td>2</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>Red</td>\n",
       "      <td>3</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>Cherry</td>\n",
       "      <td>4</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>yellow</td>\n",
       "      <td>7</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6.0</td>\n",
       "      <td>banana</td>\n",
       "      <td>8</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7.0</td>\n",
       "      <td>meat</td>\n",
       "      <td>12</td>\n",
       "      <td>17.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8.0</td>\n",
       "      <td>beef</td>\n",
       "      <td>13</td>\n",
       "      <td>14.0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9.0</td>\n",
       "      <td>pork</td>\n",
       "      <td>15</td>\n",
       "      <td>16.0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>cherry2</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>NaN</td>\n",
       "      <td>cherry3</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>bana2</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id     name   lft   rgt  father\n",
       "0   1.0     Food     1  18.0       0\n",
       "1   2.0    Fruit     2  11.0       1\n",
       "2   3.0      Red     3   6.0       2\n",
       "3   4.0   Cherry     4   5.0       3\n",
       "4   5.0   yellow     7  10.0       2\n",
       "5   6.0   banana     8   9.0       5\n",
       "6   7.0     meat    12  17.0       1\n",
       "7   8.0     beef    13  14.0       7\n",
       "8   9.0     pork    15  16.0       7\n",
       "9   NaN  cherry2  10.0  11.0       5\n",
       "10  NaN  cherry3   6.0   7.0       3\n",
       "11  NaN    bana2   6.0   7.0       3"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# -----pandas插入新数据---------\n",
    "# for i in father_nodes:\n",
    "#   id = df.query('(rgt=={})'.format(i))['father']\n",
    "  \n",
    "#    new.query('father=={}'.format(id)).isna().sum()['lft']\n",
    "\n",
    "new =  df.append(data,ignore_index=True)\n",
    "for node in data:\n",
    "  #获取同级节点\n",
    "  level_nodes = new.query('father=={}'.format(node['father']))\n",
    "  print(level_nodes,'同级节点')\n",
    "  #获取改动前末节点右值\n",
    "  end_rgt = level_nodes.dropna().iloc[-1]['rgt']\n",
    "  \n",
    "  edit_id = level_nodes[level_nodes.isna()['rgt']].index[0]\n",
    "  print(edit_id,'编辑ID',new.loc[edit_id],'对应原表节点',end_rgt,'end值')\n",
    "  new.loc[edit_id,'lft']=end_rgt+1\n",
    "  new.loc[edit_id,'rgt']=end_rgt+2\n",
    "  \n",
    "  \n",
    "# time = df.query('(father=={})&'.format(i['father']))\n",
    "\n",
    "new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "b81cfef9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([10, 11], dtype='int64')"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new =  df.append(data,ignore_index=True)\n",
    "new.query('father=={}'.format(3)).isna().sum()['lft']\n",
    "li = new.query('father=={}'.format(3))\n",
    "# li[new.query('father=={}'.format(3)).isna()['lft']]\n",
    "li[new.query('father=={}'.format(3)).isna()['lft']].index\n",
    "# new[level_nodes.isna()['id']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d65b3ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# near_node =df.query('father=={}'.format(3)).iloc[-1]['rgt']\n",
    "\n",
    "# new =  df.append(data,ignore_index=True)\n",
    "# new =  df.append({'name':'cherry2','lft':None,'father':3},ignore_index=True)\n",
    "# new =  df.append({'name':'cherry3','lft':None,'father':3},ignore_index=True)\n",
    "# new =  df.append({'name':'bana2','lft':None,'father':5},ignore_index=True)\n",
    "\n",
    "# near_node =new.query('father=={}'.format(3)).iloc[-2]['rgt']\n",
    "# new.loc[new['lft']>near_node,'lft']+=2\n",
    "# new.loc[new['rgt']>near_node,'rgt']+=2\n",
    "# add_node = new.query('father=={}'.format(3)).index[-1]\n",
    "# new.loc[add_node,'lft']=near_node + 1\n",
    "# new.loc[add_node,'rgt']=near_node + 2\n",
    "# print(new)\n",
    "# print(new.query('father==3'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "id": "4f46178f",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# df.query('father=={}'.format(cur))\n",
    "def func_son_list(node_id:int,index:int):\n",
    "  #获取子节点的ID\n",
    "  return df.query('father=={}'.format(node_id))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "f63c497a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   id    name  lft  rgt  father  newleft  newright\n",
      "1   1    Food    1   18       0      1.0      18.0\n",
      "2   2   Fruit    2   11       1      2.0      11.0\n",
      "3   3     Red    3    6       2      3.0       6.0\n",
      "4   4  Cherry    4    5       3      4.0       5.0\n",
      "5   5  yellow    7   10       2      7.0      10.0\n",
      "6   6  banana    8    9       5      8.0       9.0\n",
      "7   7    meat   12   17       1     12.0      17.0\n",
      "8   8    beef   13   14       7     13.0      14.0\n",
      "9   9    pork   15   16       7     15.0      16.0\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame(result,columns=['id','name','lft','rgt','father'])\n",
    "df.index = df.index +1\n",
    "out_res = df.copy()\n",
    "def rebuild_tree(lft,father):\n",
    "  rgt = lft+1  \n",
    "  data = df.query('father=={}'.format(father))['id']\n",
    "  # print(data)\n",
    "  #获取子节点集合\n",
    "  for i in data:\n",
    "    # print(i,type(i))\n",
    "    rgt = rebuild_tree(rgt,i) #计算子节点的左右值\n",
    "  out_res.loc[father,'newleft'] = lft\n",
    "  out_res.loc[father,'newright'] = rgt\n",
    "  return rgt + 1\n",
    "rebuild_tree(1,1)\n",
    "print(out_res)\n",
    "out_res = out_res.append({'name':'test'},ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d99f948",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "f9c2574a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine('mysql+pymysql://user:12345678@localhost:3306/itsite?charset=utf8')\n",
    "# out_res.to_sql(name='intest', con=engine,if_exists='replace',index=False)\n",
    "out_res.to_sql(name='intest', con=engine,if_exists='replace',index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "id": "41e85408",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>lft</th>\n",
       "      <th>rgt</th>\n",
       "      <th>father</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Fruit</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>meat</td>\n",
       "      <td>12</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id   name  lft  rgt  father\n",
       "2   2  Fruit    2   11       1\n",
       "7   7   meat   12   17       1"
      ]
     },
     "execution_count": 245,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('father=={}'.format(1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "id": "dfb832ad",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 当前节点 Food\n",
      "上跳一次节点 4 Cherry [1, 7, 5] ['Food', 'Fruit', 'Red', 'Cherry']\n",
      "5 当前节点 yellow\n",
      "上跳一次节点 6 banana [1, 7] ['Food', 'Fruit', 'Red', 'Cherry', 'yellow', 'banana']\n",
      "7 当前节点 meat\n",
      "节点 7 Empty DataFrame\n",
      "Columns: [id, name, lft, rgt, father]\n",
      "Index: []\n",
      "[1] 倒序插入后\n",
      "stack [1] res ['Food', 'Fruit', 'Red', 'Cherry', 'yellow', 'banana', 'meat'] idx 0\n",
      "子节点集\n",
      "    id  name  lft  rgt  father\n",
      "8   8  beef   13   14       7\n",
      "9   9  pork   15   16       7\n",
      "上跳一次节点 8 beef [1] ['Food', 'Fruit', 'Red', 'Cherry', 'yellow', 'banana', 'meat', 'beef']\n",
      "1 当前节点 Food\n",
      "上跳一次节点 4 Cherry [7, 5] ['Food', 'Fruit', 'Red', 'Cherry', 'yellow', 'banana', 'meat', 'beef', 'Food', 'Fruit', 'Red', 'Cherry']\n",
      "5 当前节点 yellow\n",
      "上跳一次节点 6 banana [7] ['Food', 'Fruit', 'Red', 'Cherry', 'yellow', 'banana', 'meat', 'beef', 'Food', 'Fruit', 'Red', 'Cherry', 'yellow', 'banana']\n",
      "7 当前节点 meat\n",
      "节点 7 Empty DataFrame\n",
      "Columns: [id, name, lft, rgt, father]\n",
      "Index: []\n",
      "[] 倒序插入后\n",
      "stack [] res ['Food', 'Fruit', 'Red', 'Cherry', 'yellow', 'banana', 'meat', 'beef', 'Food', 'Fruit', 'Red', 'Cherry', 'yellow', 'banana', 'meat'] idx 0\n",
      "子节点集\n",
      "    id  name  lft  rgt  father\n",
      "8   8  beef   13   14       7\n",
      "9   9  pork   15   16       7\n",
      "上跳一次节点 8 beef [] ['Food', 'Fruit', 'Red', 'Cherry', 'yellow', 'banana', 'meat', 'beef', 'Food', 'Fruit', 'Red', 'Cherry', 'yellow', 'banana', 'meat', 'beef']\n"
     ]
    },
    {
     "ename": "IndexError",
     "evalue": "pop from empty list",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mIndexError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_3980\\57815729.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     38\u001b[0m   \u001b[0mres\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcur\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'name'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     39\u001b[0m   \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'上跳一次节点'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mcur\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcur\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'name'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mstack\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mres\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 40\u001b[1;33m   \u001b[0mcur\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mstack\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpop\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     41\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     42\u001b[0m     \u001b[1;31m# father = stack[-1]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mIndexError\u001b[0m: pop from empty list"
     ]
    }
   ],
   "source": [
    "stack = [1]\n",
    "res = []\n",
    "cur = 1\n",
    "idx = 0\n",
    "# while cur or stack:\n",
    "  #当前节点是子节点集的末尾\n",
    "\n",
    "while cur or stack: #节点有效\n",
    "  print(cur,'当前节点',df.loc[cur]['name'])\n",
    "  #子节点为 df.query('father=={}'.format(cur)).iloc[idx]\n",
    "  \n",
    "  # while cur != df.query('father=={}'.format(cur)).iloc[-1]['id']:\n",
    "  # res.append( df.query('father=={}'.format(cur)).iloc[0]['name'])\n",
    "  while cur in df['father'].unique(): #节点存在子节点\n",
    "      # stack.append(cur)\n",
    "      if cur>6:\n",
    "        print('节点',cur ,df.query('(father=={})&(id>{})'.format(df.loc[cur]['father'],cur)) )\n",
    "      if df.query('(father=={})'.format(df.loc[cur]['father'])).shape[0] >0 and df.query('(father=={})'.format(df.loc[cur]['father'],cur))['id'].iloc[-1]!=cur: # >0 \n",
    "        #存在多个同级点，则倒序入栈\n",
    "        approach_nodes = df.query('(father=={})'.format(df.loc[cur]['father'])).sort_index(ascending=False)['id'].iloc[:-1]\n",
    "        for i in approach_nodes:\n",
    "          stack.append(i)\n",
    "        # either_node = df.query('(father=={})'.format(cur)).iloc[0]['id']\n",
    "        # stack.append(either_node)\n",
    "      if cur >6:\n",
    "        print(stack,'倒序插入后')\n",
    "      res.append(df.loc[cur]['name'])\n",
    "      # res.append(df.loc[cur]['name'])\n",
    "      if cur >6:\n",
    "        print('stack',stack,'res',res,'idx',idx)\n",
    "        print('子节点集\\n',df.query('father=={}'.format(cur))) #显示子\n",
    "      #获取第一个子节点\n",
    "      cur = df.query('(father=={})'.format(cur)).iloc[0]['id']\n",
    "      # cur = func_son_list(cur).iloc[0]['id']\n",
    "      # cur = df.query('father=={}'.format(cur)).iloc[0]['id']\n",
    "  #无子，计算右值，切换同级下个节点\n",
    "  # rgt = lft + 1\n",
    "  res.append(df.loc[cur]['name'])\n",
    "  print('上跳一次节点',cur,df.loc[cur]['name'],stack,res)\n",
    "  cur = stack.pop()\n",
    "  \n",
    "    # father = stack[-1]\n",
    "    #当前节点在同级节点中的索引 = 总长度 - 余下的元素个数\n",
    "    # len = df.query('(father=={})'.format(father)).shape[0]\n",
    "    # surplus = df.query('(father=={})&(id>{})'.format(father,cur)).shape[0]\n",
    "    # idx = len - surplus \n",
    "    # print('idx',idx,'父节点id',father,df.loc[father]['name'],res)\n",
    "    \n",
    "    #右侧没有同级节点，上跳父节点\n",
    "  # cur = stack.pop()\n",
    "  \n",
    "  # if stack:\n",
    "  # #切换到父节点的第idx子节点\n",
    "  #   cur = df.query('(father=={})'.format(father)).iloc[idx]['id']\n",
    "print(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "id": "958116e8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7\n"
     ]
    }
   ],
   "source": [
    "approach_nodes = df.query('(father=={})'.format(df.loc[7]['father'])).sort_index(ascending=False)['id'].iloc[:-1]\n",
    "for i in approach_nodes:\n",
    "  print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "id": "dfb832ad",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    }
   ],
   "source": [
    "while df.query('(father=={})'.format(father)).shape[0] <= idx or stack:#当前节点是子节点集的末尾\n",
    "\n",
    "  while idx <= df.query('father=={}'.format(cur)).shape[0]: #未循环完当前节点的子\n",
    "    print(cur,'当前节点',df.loc[cur]['name'],idx,'idx')\n",
    "    # while cur != df.query('father=={}'.format(cur)).iloc[-1]['id']:\n",
    "    # res.append( df.query('father=={}'.format(cur)).iloc[0]['name'])\n",
    "    while cur in df['father'].unique(): #当前节点存在子节点\n",
    "        stack.append(cur)\n",
    "        res.append(df.loc[cur]['name'])\n",
    "        # res.append(df.loc[cur]['name'])\n",
    "        print('stack',stack,'res',res,'idx',idx)\n",
    "        print('子节点集\\n',df.query('father=={}'.format(cur))) #显示子\n",
    "        #获取第一个子节点\n",
    "        cur = df.query('father=={}'.format(cur)).iloc[0]['id']\n",
    "    #无子，计算右值，切换同级下个节点\n",
    "    res.append(df.loc[cur]['name'])\n",
    "    father = stack[-1]\n",
    "    #cur = 当前节点(叶节点)\n",
    "    #当前节点在同级节点中的索引 = 总长度 - 余下的元素个数\n",
    "    len = df.query('(father=={})'.format(father)).shape[0]\n",
    "    surplus = df.query('(father=={})&(id>{})'.format(father,cur)).shape[0]\n",
    "    idx = len - surplus \n",
    "    print('idx',idx,'父节点id',father,df.loc[father]['name'],res)\n",
    "    \n",
    "    #右侧没有同级节点，上跳父节点\n",
    "  father = stack.pop()\n",
    "  print('上跳一次父节点',father,df.loc[father]['name'],stack)\n",
    "  if stack:\n",
    "  #切换到父节点的第idx子节点\n",
    "    cur = df.query('(father=={})'.format(father)).iloc[idx]['id']\n",
    "print(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b37e9d36",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "outputs": [],
   "source": [
    "\n",
    "while stack or cur in df['id']:\n",
    "  if cur in df['id']:\n",
    "    res.append(df.loc[cur]['name'])\n",
    "\n",
    "    if df.query('father=={}'.format(cur)).shape[0] == 1:\n",
    "      #cur有一个子\n",
    "      cur = df.query('father=={}'.format(cur)).iloc[0]['id']\n",
    "      print('当前',cur,res,'一个子')\n",
    "      # stack.append()\n",
    "    elif df.query('father=={}'.format(cur)).shape[0] > 1:\n",
    "      #有多个子\n",
    "      cur = df.query('father=={}'.format(cur)).iloc[0]['id']\n",
    "      second_node = df.query('(father=={})&(id>{})'.format(df.loc[cur]['father'],cur))\n",
    "      stack.append(second_node)\n",
    "      print('当前',cur,res,'多个子')\n",
    "    elif df.query('father=={}'.format(cur)).shape[0] == 0:\n",
    "      curr = False #切换右侧节点\n",
    "      \n",
    "  else:\n",
    "      curr = stack.pop()\n",
    "      # cur = False\n",
    "  print(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "id": "54caf68b",
   "metadata": {},
   "outputs": [],
   "source": [
    "stack.append(False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76985b1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "stack = []\n",
    "res = []\n",
    "cur = 1\n",
    "idx = 0\n",
    "next_node =1\n",
    "df.query('father=={}'.format(cur))\n",
    "def func_get_son(node_id:int,index:int):\n",
    "  #获取子节点的ID\n",
    "  return df.query('father=={}'.format(node_id)).iloc[index]\n",
    "# while df.query('(father=={})'.format(father)).shape[0] <= idx :#当前节点是子节点集的末尾\n",
    "\n",
    "# while idx <= df.query('father=={}'.format(cur['father'])).shape[0] or stack: #未循环完当前节点的子\n",
    "while cur in df['father'].unique() or stack:\n",
    "  # stack.append(cur)\n",
    "  print(cur,'**OUTSIDE** 当前节点',df.loc[cur]['name'],idx,'idx')\n",
    "  # while cur != df.query('father=={}'.format(cur)).iloc[-1]['id']:\n",
    "  # res.append( df.query('father=={}'.format(cur)).iloc[0]['name'])\n",
    "  while df.query('(father=={})&(id>{})'.format(df.loc[cur]['father'],cur)).shape[0] > 0 or bool(res)==False: \n",
    "    #循环同级节点\n",
    "    print(cur,'循环同级，节点',df.loc[cur]['name'],idx,'idx',stack,'stack')\n",
    "    while cur in df['father'].unique(): #当前节点存在子节点\n",
    "        stack.append(cur)\n",
    "        res.append(df.loc[cur]['name'])\n",
    "        # res.append(df.loc[cur]['name'])\n",
    "        print('stack',stack,'res',res,'idx',idx)\n",
    "        print('子节点集\\n',df.query('father=={}'.format(cur))) #显示子\n",
    "        #获取第一个子节点\n",
    "        cur = df.query('father=={}'.format(cur)).iloc[0]['id']\n",
    "    #无子，计算右值，切换同级下个节点\n",
    "    res.append(df.loc[cur]['name'])\n",
    "    father = stack[-1]\n",
    "    # father = df.loc[cur]['father']\n",
    "    #cur = 当前节点(叶节点)\n",
    "    #当前节点在同级节点中的索引 = 总长度 - 余下的元素个数\n",
    "    len = df.query('(father=={})'.format(father)).shape[0]\n",
    "    surplus = df.query('(father=={})&(id>{})'.format(father,cur)).shape[0]\n",
    "    idx = len - surplus\n",
    "    if df.query('(father=={})&(id>{})'.format(father,cur)).shape[0] > 0:\n",
    "      cur = df.query('(father=={})&(id>{})'.format(father,cur)).iloc[0]   \n",
    "    # if df.query('(father=={})&(id>{})'.format(father,cur)).shape[0] > 0:\n",
    "    #   next_node = df.query('(father=={})&(id>{})'.format(father,cur)).iloc[0]\n",
    "    #   print('下个节点',next_node)\n",
    "    #   cur = next_node\n",
    "    # else:\n",
    "    #   next_node = False\n",
    "    print('idx',idx,'父节点id',father,df.loc[father]['name'],res)\n",
    "  #上跳至有同级的父节点\n",
    "  while df.query('(father=={})&(id>{})'.format(df.loc[cur]['father'],cur)) == 0:\n",
    "    cur = stack.pop()\n",
    "    print('上跳一次父节点,当前',cur,df.loc[cur]['name'],stack)\n",
    "  #切换父节点的同级\n",
    "\n",
    "  #右侧没有同级节点，上跳父节点\n",
    "  cur = stack.pop()\n",
    "  \n",
    "  #切换到父节点的下个同级节点\n",
    "\n",
    "  # if stack:\n",
    "  #切换到父节点的第idx子节点\n",
    "  # cur = df.query('(father=={})'.format(father)).iloc[idx]['id']\n",
    "print(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aed9782a",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# while idx <= df.query('father=={}'.format(cur['father'])).shape[0] or stack: #未循环完当前节点的子\n",
    "while cur in df['father'].unique() or stack:\n",
    "  # stack.append(cur)\n",
    "  print(cur,'**OUTSIDE** 当前节点',df.loc[cur]['name'],idx,'idx')\n",
    "  # while cur != df.query('father=={}'.format(cur)).iloc[-1]['id']:\n",
    "  # res.append( df.query('father=={}'.format(cur)).iloc[0]['name'])\n",
    "  while df.query('(father=={})&(id>{})'.format(df.loc[cur]['father'],cur)).shape[0] > 0 or bool(res)==False: \n",
    "    #循环同级节点\n",
    "    print(cur,'循环同级，节点',df.loc[cur]['name'],idx,'idx',stack,'stack')\n",
    "    while cur in df['father'].unique(): #当前节点存在子节点\n",
    "        stack.append(cur)\n",
    "        res.append(df.loc[cur]['name'])\n",
    "        # res.append(df.loc[cur]['name'])\n",
    "        print('stack',stack,'res',res,'idx',idx)\n",
    "        print('子节点集\\n',df.query('father=={}'.format(cur))) #显示子\n",
    "        #获取第一个子节点\n",
    "        cur = df.query('father=={}'.format(cur)).iloc[0]['id']\n",
    "    #无子，计算右值，切换同级下个节点\n",
    "    res.append(df.loc[cur]['name'])\n",
    "    father = stack[-1]\n",
    "    # father = df.loc[cur]['father']\n",
    "    #cur = 当前节点(叶节点)\n",
    "    #当前节点在同级节点中的索引 = 总长度 - 余下的元素个数\n",
    "    len = df.query('(father=={})'.format(father)).shape[0]\n",
    "    surplus = df.query('(father=={})&(id>{})'.format(father,cur)).shape[0]\n",
    "    idx = len - surplus\n",
    "    if df.query('(father=={})&(id>{})'.format(father,cur)).shape[0] > 0:\n",
    "      cur = df.query('(father=={})&(id>{})'.format(father,cur)).iloc[0]   \n",
    "    # if df.query('(father=={})&(id>{})'.format(father,cur)).shape[0] > 0:\n",
    "    #   next_node = df.query('(father=={})&(id>{})'.format(father,cur)).iloc[0]\n",
    "    #   print('下个节点',next_node)\n",
    "    #   cur = next_node\n",
    "    # else:\n",
    "    #   next_node = False\n",
    "    print('idx',idx,'父节点id',father,df.loc[father]['name'],res)\n",
    "  #上跳至有同级的父节点\n",
    "  while df.query('(father=={})&(id>{})'.format(df.loc[cur]['father'],cur)) == 0:\n",
    "    cur = stack.pop()\n",
    "    print('上跳一次父节点,当前',cur,df.loc[cur]['name'],stack)\n",
    "  #切换父节点的同级\n",
    "\n",
    "  #右侧没有同级节点，上跳父节点\n",
    "  cur = stack.pop()\n",
    "  \n",
    "  #切换到父节点的下个同级节点\n",
    "\n",
    "  # if stack:\n",
    "  #切换到父节点的第idx子节点\n",
    "  # cur = df.query('(father=={})'.format(father)).iloc[idx]['id']\n",
    "print(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8aa8860",
   "metadata": {},
   "outputs": [],
   "source": [
    "#备份1#\n",
    "while df.query('(father=={})'.format(father)).shape[0] <= idx or stack:#当前节点是子节点集的末尾\n",
    "\n",
    "  while idx <= df.query('father=={}'.format(cur)).shape[0]: #未循环完当前节点的子\n",
    "    print(cur,'当前节点',df.loc[cur]['name'],idx,'idx')\n",
    "    # while cur != df.query('father=={}'.format(cur)).iloc[-1]['id']:\n",
    "    # res.append( df.query('father=={}'.format(cur)).iloc[0]['name'])\n",
    "    while cur in df['father'].unique(): #当前节点存在子节点\n",
    "        stack.append(cur)\n",
    "        res.append(df.loc[cur]['name'])\n",
    "        # res.append(df.loc[cur]['name'])\n",
    "        print('stack',stack,'res',res,'idx',idx)\n",
    "        print('子节点集\\n',df.query('father=={}'.format(cur))) #显示子\n",
    "        #获取第一个子节点\n",
    "        cur = df.query('father=={}'.format(cur)).iloc[0]['id']\n",
    "    #无子，计算右值，切换同级下个节点\n",
    "    res.append(df.loc[cur]['name'])\n",
    "    father = stack[-1]\n",
    "    #cur = 当前节点(叶节点)\n",
    "    #当前节点在同级节点中的索引 = 总长度 - 余下的元素个数\n",
    "    len = df.query('(father=={})'.format(father)).shape[0]\n",
    "    surplus = df.query('(father=={})&(id>{})'.format(father,cur)).shape[0]\n",
    "    idx = len - surplus \n",
    "    print('idx',idx,'父节点id',father,df.loc[father]['name'],res)\n",
    "    \n",
    "    #右侧没有同级节点，上跳父节点\n",
    "  father = stack.pop()\n",
    "  print('上跳一次父节点',father,df.loc[father]['name'],stack)\n",
    "  if stack:\n",
    "  #切换到父节点的第idx子节点\n",
    "    cur = df.query('(father=={})'.format(father)).iloc[idx]['id']\n",
    "print(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "5d47a9a4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 5)"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# cur.isin(df['father'])\n",
    "# cur in df['father']\n",
    "# df['name']\n",
    "# df['father'].unique()\n",
    "# cur = 1\n",
    "# df.query('father=={}'.format(cur)).iloc[0]['id'] in df['father']\n",
    "# df.query('(father=={})'.format(cur)).shape[0]\n",
    "cur = 5\n",
    "df.query('(father=={})&(id>{})'.format(df.loc[cur]['father'],cur)).shape\n",
    "# .loc[idx]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "6ec780f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 3 in df['father'].unique()\n",
    "len = df.query('(father=={})'.format(2)).shape[0]\n",
    "surplus = df.query('(father=={})&(id>{})'.format(2,3)).shape[0]\n",
    "len - surplus"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c2ff51b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.8rc1 (tags/v3.7.8rc1:5f3933d61d, Jun 17 2020, 16:59:29) [MSC v.1916 64 bit (AMD64)]"
  },
  "vscode": {
   "interpreter": {
    "hash": "a2fd889ba9628a3c2f6859828645678cf870882c81baeba3174f6483c31375f1"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
